import pandas as pd
import json
from openpyxl import load_workbook
from openpyxl.styles import PatternFill

# 读取CSV文件，只保留“Name”和“Time”列
sheet1 = pd.read_csv('export_report.csv', usecols=['Name', 'Time'])

# 尝试读取Excel文件中的特定sheet页
try:
    sheet2 = pd.read_excel('rk3568.xlsx', sheet_name='RK3568_report', usecols=['Module', '中文子系统', '英文子系统'])
except FileNotFoundError:
    sheet2 = pd.DataFrame(columns=['Module', '中文子系统', '英文子系统'])  # 创建一个空DataFrame以防文件不存在

# 读取JSON文件
with open('partname-hapname.json', 'r', encoding='utf-8') as f:
    partname_hapname_list = json.load(f)

# 将JSON列表转换为一个字典，键为“hap_name”，值为“part_name”
hapname_to_partname = {item['hap_name']: item['part_name'] for item in partname_hapname_list}

# 初始化一个空列表来存储结果行
result_rows = []

# 遍历sheet1中的每一行
for index, row in sheet1.iterrows():
    name = row['Name']
    # 将Time从秒转换为分钟
    time_in_minutes = row['Time'] / 60

    # 初始化其他列的值为空或默认值
    chinese_subsystem = ''
    english_subsystem = ''
    part_name = ''

    # 在hapname_to_partname字典中查找匹配的part_name
    if name in hapname_to_partname:
        part_name = hapname_to_partname[name]

    # 在sheet2中查找匹配的Module，并获取中文子系统和英文子系统
    matched_row = sheet2[sheet2['Module'] == name]
    if not matched_row.empty:
        chinese_subsystem = matched_row.iloc[0]['中文子系统']
        english_subsystem = matched_row.iloc[0]['英文子系统']

    # 将数据添加到结果列表中
    result_rows.append({
        'Name': name,
        'Time (minutes)': time_in_minutes,  # 更新列名以反映单位变化
        'Part Name': part_name,
        '中文子系统': chinese_subsystem,
        '英文子系统': english_subsystem
    })

# 使用结果列表创建一个新的DataFrame
result_df = pd.DataFrame(result_rows)

# 将结果写入新的Excel文件，并保留文件以便后续修改
excel_path = '测试套执行时间.xlsx'
with pd.ExcelWriter(excel_path, engine='openpyxl') as writer:
    result_df.to_excel(writer, index=False, sheet_name='Sheet1')

    # 加载刚刚写入的Excel文件
    workbook = writer.book
    worksheet = writer.sheets['Sheet1']

    # 设置红色填充样式
    red_fill = PatternFill(start_color='FFFF0000', end_color='FFFF0000', fill_type='solid')

    # 遍历Time (minutes)列，设置大于等于2的单元格为红色
    for row in worksheet.iter_rows(min_row=2, min_col=2, max_col=2):  # 假设Time (minutes)在第二列
        cell = row[0]
        if cell.value is not None and cell.value >= 2:
            cell.fill = red_fill

print('匹配结果已写入 测试套执行时间.xlsx。')